package java6681.lesson11;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author Junichiro
 * @description 执行DML语句
 * @date 2021-05-23 10:00
 **/

public class SimpleDML6681 {
    private static final String URL = "jdbc:mysql:///db3?useSSL=false";
    private static final String USER = "root";
    private static final String PW = "123456";

    public static void main(String[] args) {
        /*创建*/
        String createSql = "CREATE TABLE xslist(\n" +
                "  id INT PRIMARY KEY AUTO_INCREMENT,\n" +
                "  sno TEXT NOT NULL,\n" +
                "  sname TEXT,\n" +
                "  lx1 INTEGER,\n" +
                "  lx2 INTEGER\n" +
                ");";
        execute6681(createSql);

        /*增加*/
        String insertSql = "insert into xslist(sno,sname) values('212006681','张舒婷')";
        System.out.println(execute6681(insertSql));

        /*修改*/
        String updateSql = "update xslist set lx2 =lx1 + id and lx2 =lx1 + id";
        System.out.println(execute6681(updateSql));

        /*删除*/
        String deleteSql = "DELETE FROM xslist WHERE lx1 % 3 = 0";
        System.out.println(execute6681(deleteSql));
    }

    private static int execute6681(String sqls) {
        Connection conn = null;
        Statement stmt = null;
        int count = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(URL, USER, PW);
            String sql = sqls;
            stmt = conn.createStatement();
            count = stmt.executeUpdate(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return count;
    }

}
